package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.entity.Tables.ROLE
import com.opennews.openplatform.familyexpenseapi.entity.Tables.USER_ROLE
import org.jooq.DSLContext
import org.springframework.stereotype.Service

@Service
class UserRoleService(
    private val dslContext: DSLContext,
) {
    /**
     * Queries role ids of user.
     *
     * @param accountGroupId The ID of the account group.
     * @param userId The ID of the user.
     * @return The list of role IDs.
     */
    fun queryRoleIds(accountGroupId: String, userId: String): List<String> =
        dslContext.selectFrom(USER_ROLE)
            .where(
                USER_ROLE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(USER_ROLE.USER_ID.eq(userId))
            )
            .fetch(USER_ROLE.ROLE_ID)

    /**
     * Queries roles' id, authority, and title based on provided user ID against a specific account group.
     *
     * @param accountGroupId The ID of the account group.
     * @param userId The ID of the user.
     * @return A list of maps containing:
     * - "id": String of the role ID.
     * - "authority": String of the role authority.
     * - "title": String of the role title.
     */
    fun queryFullRoles(accountGroupId: String, userId: String): List<Map<String, Any>> =
        dslContext
            .select(
                USER_ROLE.ROLE_ID.`as`("id"),
                ROLE.AUTHORITY.`as`("authority"),
                ROLE.TITLE.`as`("title"),
            )
            .from(USER_ROLE)
            .join(ROLE).on(USER_ROLE.ROLE_ID.eq(ROLE.ID))
            .where(
                USER_ROLE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(USER_ROLE.USER_ID.eq(userId))
            )
            .fetchMaps()

    /**
     * Checks if the user with the provided ID has the specific role.
     *
     * @param accountGroupId The ID of the account group.
     * @param userId The ID of the user.
     * @param roleName The name of the role to check.
     * @return True if the user has the role, false otherwise.
     */
    fun hasRole(accountGroupId: String, userId: String, roleName: String): Boolean {
        val count = dslContext.selectCount()
            .from(USER_ROLE)
            .join(ROLE).on(USER_ROLE.ROLE_ID.eq(ROLE.ID))
            .where(
                USER_ROLE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(USER_ROLE.USER_ID.eq(userId))
                    .and(ROLE.AUTHORITY.eq(roleName))
            )
            .fetchOneInto(Int::class.java)

        return (count ?: 0) > 0
    }

    /**
     * Adds roles for a user.
     *
     * @param accountGroupId The ID of the account group.
     * @param userId The ID of the user.
     * @param roleIds The list of role IDs to assign.
     */
    fun add(accountGroupId: String, userId: String, roleIds: List<String>) {
        val records = roleIds.map { roleId ->
            dslContext.newRecord(USER_ROLE).apply {
                this.accountGroupId = accountGroupId
                this.userId = userId
                this.roleId = roleId
            }
        }

        dslContext.batchInsert(records).execute()
    }

    /**
     * Deletes user roles by user ID and role IDs.
     *
     * @param accountGroupId The ID of the account group.
     * @param userId The ID of the user.
     * @param roleIds The list of role IDs to delete.
     */
    fun delete(accountGroupId: String, userId: String, roleIds: List<String>) {
        dslContext.deleteFrom(USER_ROLE)
            .where(
                USER_ROLE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(USER_ROLE.USER_ID.eq(userId))
                    .and(USER_ROLE.ROLE_ID.`in`(roleIds))
            )
            .execute()
    }
}